﻿using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.UrlHelpers;
using Sustainalytics.DataLayer.PI;
using Sustainalytics.Entities.ProductInvolvement;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Sustainalytics.BusinessResearchProductStates.Entities.ResearchState;
using MongoDB.Driver;
using Sustainalytics.Utils;

namespace Sustainalytics.ProductInvolvement.BL
{
    public class InputUpdater : IPIUpdater
    {
        private readonly string PI_FRAMEWORK = "PI Framework";
        private readonly string PI_COMPANYASSESSMENTS = "PI Company assessments";
        private readonly string[] SYMBOLS = { "AE", "AL", "CW", "FA", "FL", "GB", "GM", "MC", "NP", "PE", "TP", "AB", "CO", "SC", "PM", "PP", "AT", "PL", "TC", "AC", "OS" };

        private IClientDwRepository _dwRepository;
        private IPIIndicatorRepository _piFrameworkRepository;
        private IPICompanyAssessmentRepository _compAssessmentRepository;
        private IPICompanyAssessmentShortReportRepository _compAssessmentShortRepository;
        private IMongoCollection<CorporateData> _corporateDataCollection;

        private InputUpdater() { }

        public InputUpdater(IClientDwRepository dwRepository,
                            IPIIndicatorRepository piFrameworkRepository,
                            IPICompanyAssessmentRepository compAssessmentRepository,
                            IPICompanyAssessmentShortReportRepository compAssessmentShortRepository,
                            IMongoCollection<CorporateData> corporateDataCollection)
        {
  
            _dwRepository = dwRepository?? throw new ArgumentNullException("dwRepository");
            _piFrameworkRepository = piFrameworkRepository?? throw new ArgumentNullException("piFrameworkRepository");
            _compAssessmentRepository = compAssessmentRepository?? throw new ArgumentNullException("compAssessmentRepository");
            _compAssessmentShortRepository = compAssessmentShortRepository?? throw new ArgumentNullException("compAssessmentShortRepository");
            _corporateDataCollection = corporateDataCollection?? throw new ArgumentNullException("corporateDataCollection");

        }

        public static IPIUpdater GetInstance(
                            IClientDwRepository dwRepository,
                            IPIIndicatorRepository piFrameworkRepository,
                            IPICompanyAssessmentRepository compAssessmentRepository,
                            IPICompanyAssessmentShortReportRepository compAssessmentShortRepository,
                            IMongoCollection<CorporateData> corporateDataCollection)

            
        {
            return new InputUpdater
            {
                _dwRepository = dwRepository,
                _piFrameworkRepository = piFrameworkRepository,
                _compAssessmentRepository = compAssessmentRepository,
                _compAssessmentShortRepository = compAssessmentShortRepository,
                _corporateDataCollection = corporateDataCollection

            };
    }

        public virtual ProcessResult ProcessInputAsExcelFile(Stream file)
        {
            var result = new ProcessResult() { Success = true };

            List<PIIndicatorChange> piChanges;

            List<PICompanyChange> companies;

            var validationResult = new ValidationResult();
            
            InternalValidation(file, validationResult, out piChanges, out companies);

            result.Success = validationResult.Success;
            if (result.Success)
            {  
                // part 1 - PI framework
                ApplyPIFrameworkChanges(piChanges);

                //REMOVE Parameter
                // part 2 - Companies
                ApplyCompaniesChanges(companies);
            }
            return result;
        }
        
        public void ApplyCompaniesChanges(List<PICompanyChange> companies)
        {
            //todo Petre: may improve here speed by not loading a company more than once
            foreach (var companyChange in companies.Where(x => x.Error == PIInputValidationErrors.None))
            {
                // get company
                var companyAssessment = _compAssessmentRepository.ReadByCompanyId(companyChange.ExternalCompanyId);

                if (companyAssessment == null)
                {
                    companyAssessment = new PICompanyAssessment()
                    {
                        Id = companyChange.CompanyId,
                        CompanyName = companyChange.CompanyName,
                        Country = companyChange.Country,
                        Industry = companyChange.Industry,
                        CompanyId = companyChange.ExternalCompanyId,
                        Exchange = companyChange.Exchange,
                        Ticker = companyChange.Ticker,
                        CompanyNameLowerCase = companyChange.CompanyName.ToLowerInvariant(),
                        PeerGroupName = companyChange.PeerGroupName,
                        MarketCap = companyChange.MarketCap,
                        Employees = companyChange.Employees,
                        Indicators = new List<PIIndicatorAssessment>(),
                        CompanyNameSafeUrl = companyChange.CompanyNameSafeUrl
                    };
                }

                /////////
                companyAssessment.Exchange = companyChange.Exchange;
                companyAssessment.Ticker = companyChange.Ticker;
                companyAssessment.CompanyNameLowerCase = companyChange.CompanyName.ToLowerInvariant();
                companyAssessment.PeerGroupName = companyChange.PeerGroupName;
                companyAssessment.MarketCap = companyChange.MarketCap;
                companyAssessment.Employees = companyChange.Employees;
                companyAssessment.CompanyNameSafeUrl = companyChange.CompanyNameSafeUrl;
                /////////

                if (companyChange.Operation == PIInputOperations.Create ||
                    companyChange.Operation == PIInputOperations.Update)
                {
                    UpdateCompanyAssessment(companyChange, companyAssessment);
                    _compAssessmentRepository.Update(companyAssessment);
                }
                else if (companyChange.Operation == PIInputOperations.Delete)
                {
                    RemoveCompanyAssessment(companyChange, companyAssessment);

                    //remove whole Assessment if we don't have indicators available anymore
                    if (!companyAssessment.Indicators.Any())
                    {
                        _compAssessmentRepository.Delete(companyAssessment);
                    }
                    else //only update if we have indicators left
                    {
                        _compAssessmentRepository.Update(companyAssessment);
                    }
                }
            }
        }

        private void RemoveCompanyAssessment(PICompanyChange companyChange, PICompanyAssessment companyAssessment)
        {
            var indicatorAssessment = companyAssessment.Indicators.FirstOrDefault(x => x.CategorySymbol == companyChange.CompanyChangeElement.CategorySymbol);

            if (indicatorAssessment != null)
            {
                //remove indicator
                companyAssessment.Indicators.Remove(indicatorAssessment);
            }

            //remove short report
            RemovePICompanyShortReport(companyChange);
        }

        private void UpdateCompanyAssessment(PICompanyChange companyChange, PICompanyAssessment companyAssessment)
        {
            //we should also validate this in upload PI excel from Admin
            if (!string.IsNullOrEmpty(companyChange.CompanyChangeElement.IndicatorSymbol) && !string.IsNullOrEmpty(companyChange.CompanyChangeElement.CategorySymbol))
            {
                var indicatorAssessment = companyAssessment.Indicators.FirstOrDefault(x => x.CategorySymbol == companyChange.CompanyChangeElement.CategorySymbol);

                if (indicatorAssessment != null)
                {
                    // already existing, replace
                    companyAssessment.Indicators.Remove(indicatorAssessment);
                }

                companyAssessment.Indicators.Add(companyChange.CompanyChangeElement);

                //create short report
                CreatePICompanyShortReport(companyChange, companyAssessment);
            }
        }

        public void CreatePICompanyShortReport(PICompanyChange companyChange, PICompanyAssessment companyAssessment)
        {
            //new condition
            if (companyAssessment.Id != Guid.Empty)
            {
                var report = _compAssessmentShortRepository.FindByCompanyIdAndCategory(companyChange.CompanyId, companyChange.CompanyChangeElement.CategorySymbol);

                if (report == null)
                {
                    report = new PICompanyAssessmentShortReport
                    {
                        Id = Guid.NewGuid(),
                        CompanyId = companyAssessment.Id,
                    };
                }

                //update all fields
                report.CompanyName = companyAssessment.CompanyName;
                report.Exchange = companyAssessment.Exchange;
                report.Ticker = companyAssessment.Ticker;
                report.CompanyNameLowerCase = companyAssessment.CompanyName.ToLowerInvariant();
                report.Industry = companyAssessment.Industry;
                report.PeerGroupName = companyAssessment.PeerGroupName;
                report.Country = companyAssessment.Country;
                report.IndicatorSymbol = companyChange.CompanyChangeElement.IndicatorSymbol;
                report.CategorySymbol = companyChange.CompanyChangeElement.CategorySymbol;
                report.CompanyNameSafeUrl = companyChange.CompanyNameSafeUrl;
                report.Score = companyChange.CompanyChangeElement.Score;
                report.LevelOfInvolvement = companyChange.CompanyChangeElement.LevelOfInvolvement;


                var stop = companyChange.CompanyChangeElement.LevelOfInvolvement.Stop;

                if (!stop.NearlyEq(100) && (stop % 1).NearlyEq(0))
                {                    
                    stop -= 0.1;
                }

                var strRangeOwnership = "N/A";
                if (companyChange.CompanyChangeElement.HasOwnershipPercent && 
                    companyChange.CompanyChangeElement.OwnershipPercent > 0)
                {
                    strRangeOwnership = string.Format("{0:N2}%", (companyChange.CompanyChangeElement.OwnershipPercent * 100));
                }

                if (companyChange.CompanyChangeElement.HasLevelOfInvolvement && 
                    !companyChange.CompanyChangeElement.LevelOfInvolvement.Start.NearlyEq(-1.00) && 
                    !companyChange.CompanyChangeElement.LevelOfInvolvement.Stop.NearlyEq(-1.00))
                {
                    strRangeOwnership = string.Format("{0}-{1}%", companyChange.CompanyChangeElement.LevelOfInvolvement.Start.ToString("G", System.Globalization.CultureInfo.InvariantCulture), stop);
                }

                report.RangeOwnership = strRangeOwnership;

                var indList = _piFrameworkRepository.ReadAll();

                var indicator = indList.FirstOrDefault(x => x.Symbol == companyChange.CompanyChangeElement.IndicatorSymbol);
                report.IndicatorSymbolDescription = indicator != null ? indicator.Name : "";

                var cat = indicator.Categories.FirstOrDefault(x => x.Symbol == companyChange.CompanyChangeElement.CategorySymbol);
                report.CategoryOfInvolvementDescription = cat.Description;
                _compAssessmentShortRepository.Update(report);
            }
        }

        public void RemovePICompanyShortReport(PICompanyChange companyChange)
        {
            var report = _compAssessmentShortRepository.FindByCompanyIdAndCategory(companyChange.CompanyId, companyChange.CompanyChangeElement.CategorySymbol);

            if (report != null)
                _compAssessmentShortRepository.Delete(report);
        }

        public void ApplyPIFrameworkChanges(List<PIIndicatorChange> piChanges)
        {
            var toUpdate = new List<PIIndicator>();

            var toCreate = new List<PIIndicator>();

            var piFramework = _piFrameworkRepository.ReadAll();

            foreach (var pichange in piChanges.Where(x => x.Error == PIInputValidationErrors.None))
            {
                if (pichange.Operation == PIInputOperations.Create ||
                    pichange.Operation == PIInputOperations.Update)
                {
                    var current = piFramework.FirstOrDefault(x => x.Symbol == pichange.Indicator.Symbol);

                    if (current != null)
                    {
                        pichange.Indicator.Id = current.Id;
                        toUpdate.Add(pichange.Indicator);
                    }
                    else
                    {
                        pichange.Indicator.Id = Guid.NewGuid();
                        toCreate.Add(pichange.Indicator);
                    }
                }
                // TODO: implement delete
            }

            _piFrameworkRepository.Update(toUpdate);
            _piFrameworkRepository.Create(toCreate);
        }

        public virtual void ValidateCompanyAssessment(PICompanyChange compChange, List<PIIndicator> piFramework)
        {
            compChange.Details = "CapitalIQId: " + compChange.ExternalCompanyId + " |";
            if (compChange.Operation == PIInputOperations.None)
            {
                compChange.Details += "Operation unknown |";
                compChange.Error = PIInputValidationErrors.CA_OperationUnknown;
                return;
            }

            if (compChange.ExternalCompanyId == 0)
            {
                compChange.Details += "CapitalIQID is empty |";
                compChange.Error = PIInputValidationErrors.CA_CapitalIQIDEmpty;
                return;
            }

            var cd = _dwRepository.GetCorporateDataByCapitalIQID(compChange.ExternalCompanyId);

            if (cd == null)
            {
                compChange.Details += "CapitalIQID not found |";
                compChange.Error = PIInputValidationErrors.CA_CapitalIQIDNotFound;
                return;
            }            
            else if (cd.ProductInvolvementResearchState.ResearchState != ResearchState.ResearchAvailable)
            {
                compChange.Details += "CapitalIQID doesn't exist in PI study universe.";
                compChange.Error = PIInputValidationErrors.CA_CapitalIQIDNotFound;
                return;
            }
            else
            {
                compChange.CompanyId = cd.Id;
                compChange.CompanyName = cd.CompanyName;
                compChange.CompanyNameLowerCase = cd.CompanyNameLowercase;
                compChange.Country = cd.Country;
                compChange.Industry = cd.SustainalyticsIndustry;
                compChange.PeerGroupName = cd.PeerGroup;
                compChange.Employees = cd.TotalEmployees;

                UrlFriendlyGenerator friendlyUrl = new UrlFriendlyGenerator();
                compChange.CompanyNameSafeUrl = friendlyUrl.GetFriendlyNameById(cd.Id, cd.CompanyName);
            }

            var fd = _dwRepository.ListCompaniesFinancialData(new List<Guid>() { compChange.CompanyId }).FirstOrDefault();

            if (fd != null)
            {
                compChange.MarketCap = fd.MarketCapitalization;
            }

            //read ticker info
            var corpData = _dwRepository.GetCorporateDataByCompanyId(compChange.CompanyId);

            if (corpData != null && !string.IsNullOrEmpty(corpData.MainExchange) && !string.IsNullOrEmpty(corpData.MainTicker))
            {
                compChange.Exchange = corpData.MainExchange;
                compChange.Ticker = corpData.MainTicker;
            }

            if (!SYMBOLS.Contains(compChange.CompanyChangeElement.IndicatorSymbol))
            {
                compChange.Details += "Indicator not found in framework |";
                compChange.Error = PIInputValidationErrors.CA_IndicatorIdNotFound;
                return;
            }

            if (!piFramework.CategorySymbolExists(compChange.CompanyChangeElement.IndicatorSymbol, compChange.CompanyChangeElement.CategorySymbol))
            {
                compChange.Details += "CategoryId not found |";
                compChange.Error = PIInputValidationErrors.CA_CategoryIdNotFound;
                return;
            }
            if (string.IsNullOrEmpty(compChange.CompanyChangeElement.CategorySymbol))
            {
                compChange.Details += "CategoryId is empty |";
                compChange.Error = PIInputValidationErrors.CA_CategoryIdEmpty;
                return;
            }

            //read RevenueRangeYesNo and OwnershipYesNo from newly modified PI framework rather than excel
            var indicator = piFramework.FirstOrDefault(x => x.Symbol == compChange.CompanyChangeElement.IndicatorSymbol);

            var icat = indicator.Categories.FirstOrDefault(y => y.Symbol == compChange.CompanyChangeElement.CategorySymbol);
            compChange.CompanyChangeElement.HasLevelOfInvolvement = icat.RevenueRangeYesNo;
            compChange.CompanyChangeElement.HasOwnershipPercent = icat.OwnershipYesNo;

            #region Validate Level of Involvement and Ownership Percent

            if (compChange.CompanyChangeElement.HasLevelOfInvolvement && string.IsNullOrEmpty(compChange.CompanyChangeElement.LevelOfInvolvement.Text))
            {
                compChange.Details += "Level of involvement is empty |";
                compChange.Error = PIInputValidationErrors.CA_LevelOfInvolvementEmpty;
                return;
            }

            if (compChange.CompanyChangeElement.HasLevelOfInvolvement && (compChange.CompanyChangeElement.LevelOfInvolvement.Start.NearlyEq(-1) || compChange.CompanyChangeElement.LevelOfInvolvement.Stop.NearlyEq(-1)))
            {
                compChange.Details += "Level of involvement is invalid |";
                compChange.Error = PIInputValidationErrors.CA_LevelOfInvovmentInvalid;
                return;
            }

            if (compChange.CompanyChangeElement.HasOwnershipPercent &&
                compChange.CompanyChangeElement.OwnershipPercent.NearlyEq(-1) &&
                piFramework.OwnershipExpected(compChange.CompanyChangeElement.IndicatorSymbol, compChange.CompanyChangeElement.CategorySymbol))
            {
                compChange.Details += "Ownership percent is invalid |";
                compChange.Error = PIInputValidationErrors.CA_OwnerShipPercentEmpty;
                return;
            }

            #endregion

            //read the score from PI framework accordingly to logic
            compChange.CompanyChangeElement.Score = -1;

            if (!compChange.CompanyChangeElement.HasLevelOfInvolvement)
                compChange.CompanyChangeElement.Score = icat.Score;
            else
            {
                var revenueRange = icat.RevenueRange.Values.FirstOrDefault(r =>    
                    r.Start.NearlyEq(compChange.CompanyChangeElement.LevelOfInvolvement.Start, .01f) &&
                    r.Stop.NearlyEq(compChange.CompanyChangeElement.LevelOfInvolvement.Stop, .01f));

                if (revenueRange != null)
                    compChange.CompanyChangeElement.Score = revenueRange.Score;
                else
                {
                    compChange.Details += "No coresponding Revenue range found coresponding to Level of Involvement |";
                    compChange.Error = PIInputValidationErrors.CA_NoRevenueRangeFoundForLevelOfInvolvement;
                    return;
                }
            }

            if (string.IsNullOrEmpty(compChange.CompanyChangeElement.StandardAnalysis))
            {
                compChange.Details += "Standard analysis is empty |";
                compChange.Error = PIInputValidationErrors.CA_StandardAnalysisEmpty;
                return;
            }

            StringBuilder details = new StringBuilder(compChange.Details);
            foreach (var sc in compChange.CompanyChangeElement.Sources)
            {
                if (
                      (!string.IsNullOrEmpty(sc.Name) && sc.Type == PIDataSourceType.None) ||
                      (string.IsNullOrEmpty(sc.Name) && sc.Type != PIDataSourceType.None)
                    )
                {
                    details.Append("Source is invalid |");
                    compChange.Error = PIInputValidationErrors.CA_SourceInvalid;
                    return;
                }
            }
            compChange.Details = details.ToString();

            if (compChange.CompanyChangeElement.LastEdited == DateTime.MinValue)
            {
                compChange.Details += "Last edited field is empty or invalid |";
                compChange.Error = PIInputValidationErrors.CA_LastEditedInvalidOrEmpty;
                return;
            }
        }

        public virtual void ValidateIndicator(PIIndicatorChange indicatorChange)
        {
            // assume symbol is valid
            indicatorChange.Details += "Symbol: " + indicatorChange.Indicator.Symbol + "|";
            if (string.IsNullOrEmpty(indicatorChange.Indicator.Name))
            {
                indicatorChange.Error = PIInputValidationErrors.FS_IndicatorNameEmpty;
                return;
            }

            var catSymbols = indicatorChange.Indicator.Categories.Select(c => c.Symbol).Distinct();

            if (catSymbols.Count() != indicatorChange.Indicator.Categories.Count)
            {
                indicatorChange.Error = PIInputValidationErrors.FS_CategorySymbolDuplicate;
                return;
            }

            StringBuilder details = new StringBuilder(indicatorChange.Details);
            foreach (var c in indicatorChange.Indicator.Categories)
            {
                details.Append("CategorySymbol: " + c.Symbol + "|");
                if (string.IsNullOrEmpty(c.Symbol))
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_CategorySymbolEmpty;
                    return;
                }
                if (string.IsNullOrEmpty(c.Description))
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_DescriptionEmpty;
                    return;
                }
                if (string.IsNullOrEmpty(c.CategoryOfInvolvement))
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_CategoryOfInvolvementEmpty;
                    return;
                }
                if (!string.IsNullOrEmpty(c.RelatedCategorySymbol) && !catSymbols.Contains(c.RelatedCategorySymbol))
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_RelatedCategoryUnknown;
                    return;
                }
                if (c.OwnershipYesNo == true && c.RevenueRangeYesNo == true)
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_OwnershipRevenueBothYes;
                    return;
                }
                if (c.OwnershipYesNo == true && !c.RevenueRange.Values.All(x => x.NA))
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_OwnershipYesRevenueRange;
                    return;
                }
                if (c.RevenueRangeYesNo == true && c.RevenueRange.Values.Any(x => x.NA == false && (x.Start.NearlyEq(-1) || x.Stop.NearlyEq(-1))))
                {
                    var r = c.RevenueRange.Values.Where(x => x.NA == false && (x.Start.NearlyEq(-1) || x.Stop.NearlyEq(-1))).First();
                    details.Append("RevenueRange " + r.RangeId + " bad format|");
                    indicatorChange.Details = details.ToString();
                    indicatorChange.Error = PIInputValidationErrors.FS_RevenueRangeFormatIncorrect;
                    return;
                }
                if (c.RevenueRangeYesNo == true && c.RevenueRange.Values.All(x => x.Start >= x.Stop))
                {
                    var r = c.RevenueRange.Values.Where(x => x.Start >= x.Stop).FirstOrDefault();

                    if (r != null)
                        details.Append("RevenueRange" + r.RangeId + " incorrect range|");

                    indicatorChange.Details = details.ToString();
                    indicatorChange.Error = PIInputValidationErrors.FS_RevenueRangeFieldsInvalid;
                    return;
                }
                //Score validation
                if (c.OwnershipYesNo && c.Score < 0)
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_OwnwershipScoreFieldsInvalid;
                    details.Append("Invalid Ownership Score|");
                }
                if ((!c.OwnershipYesNo) && (!c.RevenueRangeYesNo) && c.Score < 0)
                {
                    indicatorChange.Error = PIInputValidationErrors.FS_RevenueRangeScoreFieldsInvalid;
                    details.Append("Invalid Revenue Range Score|");
                }
                if (c.RevenueRangeYesNo && c.RevenueRange.Values.Any(x => x.NA == false && x.Score < 0))
                {
                    var r = c.RevenueRange.Values.Where(x => x.NA == false && x.Score < 0).First();
                    indicatorChange.Error = PIInputValidationErrors.FS_RevenueRangeScoreFieldsInvalid;
                    details.Append("Invalid RevenueRange" + r.RangeId + " Score|");
                }
            }

            indicatorChange.Details = details.ToString();
        }

        public ValidationResult ValidateInputAsExcelFile(Stream stream)
        {
            var validationResult = new ValidationResult();

            List<PIIndicatorChange> piChanges;

            List<PICompanyChange> companies;

            InternalValidation(stream, validationResult, out piChanges, out companies);

            //PI framework
            {
                var status = new StringBuilder();
                foreach (var p in piChanges)
                {                 
                    p.Details = string.Concat(p.Details, (p.Error == PIInputValidationErrors.None) ? "OK" : " Errors found");

                    if (p.Error != PIInputValidationErrors.None)
                        status.AppendLine(p.Details);
                }
                validationResult.FrameworkDetails = status.ToString();
                validationResult.FrameworkSuccess = piChanges.Any(c => c.Error == PIInputValidationErrors.None);

                var res = piChanges.FirstOrDefault(c => c.Error != PIInputValidationErrors.None);

                if (res != null)
                    validationResult.FrameworkError = res.Error;
            }

            //PI assessments
            {
                var status = new StringBuilder();
                foreach (var v in companies)
                {                    
                    v.Details = string.Concat(v.Details, (v.Error == PIInputValidationErrors.None) ? "OK" : " Errors found");

                    if (v.Error != PIInputValidationErrors.None)
                        status.AppendLine(v.Details);
                }
                validationResult.CompaniesInputDetails = status.ToString();
                validationResult.CompaniesInputSuccess = companies.Any(c => c.Error == PIInputValidationErrors.None);

                var res = companies.FirstOrDefault(c => c.Error != PIInputValidationErrors.None);

                if (res != null)
                    validationResult.CompaniesInputError = res.Error;
            }

            return validationResult;
        }

        private void InternalValidation(Stream stream, ValidationResult validationResult,
                                        out List<PIIndicatorChange> piChanges,
                                        out List<PICompanyChange> companies)
        {
            var workbookSet = Factory.GetWorkbookSet(System.Globalization.CultureInfo.CurrentCulture);

            var workbook = workbookSet.Workbooks.OpenFromStream(stream);

            // search for the work sheets
            var hasAssessments = false;

            var hasFramework = false;
            
            for (var i = 0; i < workbook.Sheets.Count; i++)
            {
                if (workbook.Sheets[i].Name == PI_FRAMEWORK)
                    hasFramework = true;
                if (workbook.Sheets[i].Name == PI_COMPANYASSESSMENTS)
                    hasAssessments = true;                
            }
            
            if (!hasAssessments && !hasFramework)
                validationResult.GeneralError = PIInputValidationErrors.NoSheetFound;

            var piFramework = _piFrameworkRepository.ReadAll().ToList();

            piChanges = new List<PIIndicatorChange>();
            companies = new List<PICompanyChange>();

            if (hasFramework)
            {
                piChanges = CollectIndicators(workbook);
                ApplyUpdatesToFramework(piChanges, piFramework);
                validationResult.FrameworkSuccess = piChanges.Any(c => c.Error == PIInputValidationErrors.None);
            }

            if (hasAssessments)
            {
                companies = CollectCompanies(workbook, piFramework);
                validationResult.CompaniesInputSuccess = companies.Any(c => c.Error == PIInputValidationErrors.None);
            }
        }
        
        private List<PICompanyChange> CollectCompanies(IWorkbook workbook, List<PIIndicator> piFramework)
        {
            //framework result
            var result = new List<PICompanyChange>();
            workbook.Sheets[PI_COMPANYASSESSMENTS].Select();
            var sheet = workbook.ActiveWorksheet;

            var rows = ReadCompaniesElements(sheet);

            var i = 2;

            foreach (var r in rows)
            {
                var ca = new PICompanyChange()
                {
                    CompanyChangeElement = new PIIndicatorAssessment()
                    {
                        Sources = new List<PIDataSource>()
                    }
                };

                try
                {
                    if (r['A'] == "add/update")
                        ca.Operation = PIInputOperations.Update;
                    else if (r['A'] == "delete")
                        ca.Operation = PIInputOperations.Delete;

                    int.TryParse(r['B'], out var companyId);
                    ca.ExternalCompanyId = companyId;
                    ca.CompanyChangeElement.IndicatorSymbol = r['D'];
                    ca.CompanyChangeElement.CategorySymbol = r['F'];

                    ca.CompanyChangeElement.LevelOfInvolvement = PILevelOfInvolvement.FromString(r['J']);
                    ca.CompanyChangeElement.OwnershipPercent = -1;

                    if (!string.IsNullOrEmpty(r['L']))
                    {
                        var text = r['L'].Replace("%", "");

                        double doubleVal;
                        if (double.TryParse(text, out doubleVal))
                            ca.CompanyChangeElement.OwnershipPercent = doubleVal;
                    }

                    ca.CompanyChangeElement.StandardAnalysis = r['N'];

                    ca.CompanyChangeElement.Sources.Add(PIDataSource.FromStrings(r['O'], r['P']));
                    ca.CompanyChangeElement.Sources.Add(PIDataSource.FromStrings(r['Q'], r['R']));
                    ca.CompanyChangeElement.Sources.Add(PIDataSource.FromStrings(r['S'], r['T']));
                    ca.CompanyChangeElement.Sources.Add(PIDataSource.FromStrings(r['U'], r['V']));

                    ca.CompanyChangeElement.LastEdited = DateTime.MinValue;

                    DateTime dt;
                    if (DateTime.TryParse(r['W'], out dt))
                        ca.CompanyChangeElement.LastEdited = dt;

                    ca.Details = "Sheet " + PI_COMPANYASSESSMENTS + ", Row: " + i.ToString();

                    ValidateCompanyAssessment(ca, piFramework);
                }
                catch (Exception ex)
                {
                    ca.Error = PIInputValidationErrors.Other;
                    ca.Details = "Unhandled exception";
                }

                result.Add(ca);
                i++;
            }

            return result;
        }

        private List<Dictionary<char, string>> ReadCompaniesElements(IWorksheet sheet)
        {
            var result = new List<Dictionary<char, string>>();

            var columns = new char[] { 'A', 'B', 'D', 'F', 'J', 'L', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W' };

            var hasData = true;

            for (var row = 2; hasData; row++)
            {
                var k = new Dictionary<char, string>();

                foreach (var c in columns)
                {
                    k[c] = ReadCell(sheet, c, row);
                }

                hasData = !string.IsNullOrEmpty(k['B']);
                if (hasData)
                    result.Add(k);
            }
            return result;
        }

        private List<PIIndicatorChange> CollectIndicators(IWorkbook workbook)
        {
            //framework result
            var result = new List<PIIndicatorChange>();

            workbook.Sheets[PI_FRAMEWORK].Select();
            var sheet = workbook.ActiveWorksheet;

            if (ReadCell(sheet, 'A', 1).ToLowerInvariant() != "update")
            {
                result.Add(new PIIndicatorChange()
                {
                    Error = PIInputValidationErrors.FS_MissingUpdate
                });
            }
            else
            {// collect indicators, scan B column
                var i = 2;

                do
                {
                    var val = ReadCell(sheet, 'B', i);

                    if (string.IsNullOrEmpty(val))
                        break;

                    var cellcount = (int)sheet.Cells["B" + i.ToString()].MergeArea.CellCount;

                    if (SYMBOLS.Contains(val.ToUpperInvariant()))
                    {
                        var found = CollectIndicator(sheet, val.ToUpperInvariant(), i, cellcount);
                        ValidateIndicator(found);
                        result.Add(found);
                    }
                    else
                    {
                        result.Add(new PIIndicatorChange()
                        {
                            Details = "Unrecognized indicator in cell B" + i.ToString() + "= " + val,
                            Error = PIInputValidationErrors.FS_IndicatorUnexpected
                        });
                    }
                    i += cellcount;
                }
                while (true);
            }

            return result;
        }

        private PIIndicatorChange CollectIndicator(IWorksheet sheet, string symbol, int indexStart, int subElementsCount)
        {
            //populate
            var rows = ReadSubElements(sheet, indexStart, subElementsCount);

            var cats = new List<ProductInvolvementCategory>();

            var rangeCol = new char[] { 'L', 'N', 'P', 'R', 'T' };

            var scoreCol = new char[] { 'M', 'O', 'Q', 'S', 'U' };

            foreach (var row in rows)
            {
                var newcat = new ProductInvolvementCategory()
                {
                    Symbol = row['D'],
                    CategoryOfInvolvement = row['E'],
                    RelatedCategorySymbol = row['F'],
                    Description = row['G'],
                    OwnershipYesNo = ConvertToBool(row['H']),
                    RevenueRangeYesNo = ConvertToBool(row['J']),
                    RevenueRange = new Dictionary<int, PIRange>()
                };

                if (newcat.OwnershipYesNo)
                    newcat.Score = row['I'].ParseOrDefault(int.TryParse, -1);
                else if (!newcat.RevenueRangeYesNo)
                    newcat.Score = row['K'].ParseOrDefault(int.TryParse, -1);
                else
                    newcat.Score = -1;

                for (int i = 0; i < 5; i++)
                {
                    string scoreLogic = newcat.RevenueRangeYesNo ? row[scoreCol[i]] : "-1";
                    newcat.RevenueRange.Add(i + 1, PIRange.FromRangeAndScore(i + 1, row[rangeCol[i]], scoreLogic));
                }

                cats.Add(newcat);
            }

            var result = new PIIndicatorChange()
            {
                Operation = PIInputOperations.Update,// currently (nov 2014) is the only op which is defined
                Error = PIInputValidationErrors.None,
                Details = symbol + "|",
                Indicator = new PIIndicator()
                {
                    Symbol = symbol,
                    Name = ReadCell(sheet, 'C', indexStart),
                    Categories = cats
                }
            };

            return result;
        }

        private List<Dictionary<char, string>> ReadSubElements(IWorksheet sheet, int index, int count)
        {
            var result = new List<Dictionary<char, string>>();

            for (var row = index; row < index + count; row++)
            {
                var k = new Dictionary<char, string>();

                for (var column = 'D'; column <= 'U'; column++)
                    k[column] = ReadCell(sheet, column, row);

                result.Add(k);
            }
            return result;
        }

        private string ReadCell(IWorksheet sheet, char column, int index)
        {
            var cell = sheet.Cells[column.ToString() + index.ToString()];

            switch (cell.ValueType)
            {
                case SpreadsheetGear.ValueType.Number:
                case SpreadsheetGear.ValueType.Text:
                    return (cell.Value == null) ? string.Empty : cell.Value.ToString();
                    break;
            }

            return string.Empty;
        }        

        private bool ConvertToBool(string s)
        {
            return !string.IsNullOrEmpty(s) && s.ToLowerInvariant() == "yes";
        }

        private void ApplyUpdatesToFramework(List<PIIndicatorChange> changes, List<PIIndicator> current)
        {
            foreach (var newIndicator in changes)
            {
                if (newIndicator.Error == PIInputValidationErrors.None && newIndicator.Operation == PIInputOperations.Update)
                {
                    current.RemoveAll(e => e.Symbol == newIndicator.Indicator.Symbol);
                    current.Add(newIndicator.Indicator);
                }
                else
                {
                    // log
                }
            }
        }
    }

    internal static class ValidateHelper
    {
        public static bool OwnershipExpected(this List<PIIndicator> piFramework, string indicatorSymbol, string categorySymbol)
        {
            var ind = piFramework.FirstOrDefault(indicator => indicator.Symbol == indicatorSymbol);

            if (ind != null && ind.Categories != null)
            {
                var cs = ind.Categories.FirstOrDefault(e => e.Symbol == categorySymbol);

                if (cs != null)
                    return cs.OwnershipYesNo;
            }
            return false;
        }

        public static bool CategorySymbolExists(this List<PIIndicator> piFramework, string indicatorSymbol, string categorySymbol)
        {
            return piFramework.Exists(indicator => indicator.Symbol == indicatorSymbol && indicator.Categories.Exists(cs => cs.Symbol == categorySymbol));
        }
    }
}

/*

The Framework sheet of the Excel file contains the following information:
An ‘update’ column where the user will indicate that they would like to make changes at indicator level.

At that time, all the previous entries for that indicator should be deleted and replaced with the info in the rows of the new file.
If the user chooses to delete one of the categories of the indicator, all the information displayed in the client interface related
to that category ID should be deleted.

If the user still includes information related to that category ID on the company assessments sheet of the file,
processing those companies should fail Indicator symbol – any updates made to the entries in this column should be ignored,
the current IDs (AE, AL, CW, FA, FL, GB, GM, MC, NP, PE, TP, AB, CO, SC) should be hardcoded and the user should not be allowed
to change them or add new ones Indicator name – can be edited by user,

if the user leaves this item blank by mistake, processing that indicator
should fail Category symbol – can be edited by user in the sense that they can add, delete or edit existing IDs; the restriction here should be
that they need to be unique, in the event that a user enters 2 categories with the same ID, processing that indicator should fail

Category of involvement - can be edited by user, if the user leaves this item blank by mistake, processing that indicator should fail Related category –
can be edited by user, a validation should be made so that the user does not enter a category ID that does not exist for that indicator Description –
can be edited by user, if the user leaves this item blank by mistake, processing that indicator should fail

Ownership Yes/No and Revenue range Yes/No - can be edited by user, if they select Yes in both columns for a category ID, processing that
 indicator should fail Revenue Range 1-5 – the user can define different ranges for each indicator. The ranges need to be entered in
 the following format x-y% (if not in this format, processing that indicator should fail). The ranges entered should apply to all
 categories within the indicator. All categories of the type Ownership should have n/a for all revenue ranges (the user should not
 be allowed to enter a different value in these fields for categories where Ownership was set to Yes – processing the indicator should
 fail in this case). The user should also be able to define which of the revenue ranges of the indicator do not apply for a category of
 the type Revenue range by entering n/a in that cell.

 */
